-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		TuyenVD
-- Create date: 3/16/2011
-- Description:	This stored procedure use to update an existing Role
-- =============================================
ALTER PROCEDURE UpdateRole 
	-- Add the parameters for the stored procedure here
(
@RoleID int = 0,
@RoleName nvarchar(MAX) = null,
@RoleCode varchar(MAX) = null, 
@BriefDescription NVARCHAR(500),
@FullDescription NVARCHAR(MAX),
@AuthorizationIDList NVARCHAR(MAX) = NULL,
@Result BIT = -1 OUT
)	 
AS
BEGIN
	IF (NOT EXISTS (SELECT 1 FROM [Role] WHERE @RoleCode = RoleCode AND RoleID <> @RoleID))
	BEGIN
		UPDATE [Role]
		SET
			[RoleName] = @RoleName,
			[RoleCode] = @RoleCode,
			[BriefDescription] = @BriefDescription,
			[FullDescription] = @FullDescription
		WHERE RoleID = @RoleID

		SET @Result = 0

		IF (EXISTS (SELECT 1 FROM [Role] WHERE @RoleID = RoleID AND RoleCode = @RoleCode))
		BEGIN
			SET @Result = 1
			--------------------------------------
			DELETE FROM [DefaultRoleAuthorization] 
			WHERE RoleID = @RoleID
			------------------------------
			DECLARE @XmlPointer INT
	 
			EXEC sp_xml_preparedocument @XmlPointer OUTPUT, @AuthorizationIDList

			INSERT INTO 
					DefaultRoleAuthorization(RoleID, AuthorizationID)
			SELECT 
					@RoleID
					, Data.ID
			FROM OpenXml(@XmlPointer, 'Data/Item', 2)
			WITH (ID INT) AS Data
			 
			INNER JOIN --check of the valid ID from the list
					[Authorization] a
			ON
					a.[AuthorizationID] = Data.[ID]
					
			EXEC sp_xml_removedocument @XmlPointer	
			------------------------------
		END
	END
END
GO

/*
USE WarehouseManagement
GO

SELECT * FROM [Role]
WHERE RoleID = 3
GO

EXEC UpdateRole @RoleID=3, @RoleCode = 'R03', @RoleName='Worker' , @FullDescription='The person works in the warehouse' , @BriefDescription='The person works in the warehouse', @AuthorizationIDList = '<Data><Item><ID>1</ID></Item></Data>'
GO

SELECT * FROM [Role]
WHERE RoleID = 3
SELECT * FROM DefaultRoleAuthorization
*/